﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BYS.AAPT.Common;
using LinqToExcel;
using LinqToExcel.Query;
using System.IO;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using EntityFramework.Extensions;
using System.Collections.ObjectModel;

namespace BYS.XiAnComDept.BussLogic
{
    public class FundBll : BllBase
    {
        public FundBll(ComDeptContext db) : base(db) { }

        public List<Fund> QueryFunds(int year, string companyName, IEnumerable<FundType> fundTypes)
        {
            string[] fundTypeNames = fundTypes.Select(x => x.FundTypeName).ToArray();
            List<Fund> fundData = this.Db.Funds.Where(x => (year <= 0 || x.Year.Year == year)
                     && (companyName.Trim().Length == 0 || x.Company.CompanyName.Contains(companyName) || x.Company.CompanyPreName.Contains(companyName))
                     && fundTypeNames.Contains(x.FundType.FundTypeName))
                     .Include(x => x.Company)
                     .Include(x => x.ProjectFields)
                     .Include(x => x.FundType)
                     .ToList();
            return fundData;
        }

        public Result ImportFunds(string excelFileName)
        {
            Result result = new Result(true);
            // file exists?
            if (!File.Exists(excelFileName))
            {
                return new Result(false, "Excel 文件不存在！");
            }

            try
            {
                ExcelQueryFactory excel = new ExcelQueryFactory(excelFileName);
                IEnumerable<string> worksheetNames = excel.GetWorksheetNames();

                int countCompaniesImprted = 0;
                int countFundImported = 0;
                int countFundUpdated = 0;
                int countFundTypesImported = 0;

                if (worksheetNames.Count() == 0)
                {
                    return new Result(false, "Excel 文件没有资金数据！");
                }
                List<Row> rows = new List<Row>();
                foreach (string worksheetName in worksheetNames)
                {
                    IEnumerable<Row> rowsForWorksheet = (from a in excel.Worksheet(worksheetName) select a);
                    if (rowsForWorksheet.Count() > 0)
                    {
                        List<string> columnsInExcel = rowsForWorksheet.First().ColumnNames.ToList();
                        string validColumnsErrorMsg = ValidateColumns(worksheetName, columnsInExcel);
                        if (validColumnsErrorMsg.Length != 0)
                        {
                            throw new Exception(validColumnsErrorMsg);
                        }
                        rows.AddRange(rowsForWorksheet);
                    }
                }

                if (rows.Count == 0)
                {
                    return new Result(false, "Excel 文件没有资金数据！");
                }

                string[] companyNames = Db.Companies.Select(x => x.CompanyName.Trim()).AsNoTracking().ToArray();
                IEnumerable<Company> companiesNeedAdd = rows.Where(x => !companyNames.Contains(x["单位名称"].ToString().Trim()))
                    .Select(y => y["单位名称"].ToString().Trim())
                    .Distinct()
                    .Select(z =>
                        new Company()
                        {
                            CompanyName = z
                        });
                countCompaniesImprted = companiesNeedAdd.Count();
                if (countCompaniesImprted > 0)
                {
                    Db.Companies.AddRange(companiesNeedAdd);
                }

                string[] fundTypeNames = Db.FundTypes.Select(x => x.FundTypeName.Trim()).AsNoTracking().ToArray();
                IEnumerable<FundType> fundTypesNeedAdd = rows.Where(x => !fundTypeNames.Contains(x["项目类别"].ToString().Trim()))
                    .Select(y => y["项目类别"].ToString().Trim())
                    .Distinct()
                    .Select(z =>
                        new FundType()
                        {
                            FundTypeName = z
                        });
                countFundTypesImported = fundTypesNeedAdd.Count();
                if (countFundTypesImported > 0)
                {
                    Db.FundTypes.AddRange(fundTypesNeedAdd);
                }
                Db.SaveChanges();

                foreach (Row row in rows)
                {
                    string companyName = row["单位名称"].ToString().Trim();
                    Company company = Db.Companies.SingleOrDefault(x => x.CompanyName.Trim() == companyName);
                    string fundTypeName = row["项目类别"].ToString().Trim();
                    FundType fundType = Db.FundTypes.SingleOrDefault(x => x.FundTypeName.Trim() == fundTypeName);
                    int year = Convert.ToInt32(row["年度"].ToString());

                    IEnumerable<Fund> fundsInDb = Db.Funds
                        .Include(x => x.Company)
                        .Include(x => x.FundType)
                        .Include(x => x.ProjectFields)
                        .Where(x => x.Year.Year == year
                                                      && x.FundType.FundTypeName == fundTypeName
                                                      && x.Company.ComGuid == company.ComGuid)
                                                      .ToList();
                    Fund fundToSave = null;

                    // get all the project fields in excel row.
                    List<ProjectField> projectFieldsOfExcelRow = new List<ProjectField>();
                    IEnumerable<string> projectColumns = row.ColumnNames.Where(x => x.StartsWith("project_"));
                    foreach (string colName in projectColumns)
                    {
                        ProjectField projectFieldToAdd = new ProjectField()
                        {
                            FieldName = colName.Replace("project_", ""),
                            FieldValue = row[colName].ToString(),
                            OrderNo = projectFieldsOfExcelRow.Count()
                        };
                        projectFieldsOfExcelRow.Add(projectFieldToAdd);
                    }

                    if (fundsInDb == null && fundsInDb.Count() == 0)
                    {
                        // if no this fund data exist in db, we create a new one.
                        fundToSave = new Fund();
                        Db.Funds.Add(fundToSave);
                        countFundImported++;
                    }
                    else
                    {
                        // if check if current excel row's project fields save with any fund data in db.
                        Fund fundToUpdate = fundsInDb.SingleOrDefault(x => x.HasSameProjectFields(projectFieldsOfExcelRow));
                        if (fundToUpdate != null)
                        {
                            fundToSave = fundToUpdate;
                            Db.ProjectFields.Where(x => x.Fund.FundGuid == fundToSave.FundGuid).Delete();
                            countFundUpdated++;
                        }
                        else
                        {
                            fundToSave = new Fund();
                            Db.Funds.Add(fundToSave);
                            countFundImported++;
                        }
                    }
                    foreach (ProjectField projectFieldToAdd in projectFieldsOfExcelRow)
                    {
                        fundToSave.ProjectFields.Add(projectFieldToAdd);
                        Db.ProjectFields.Add(projectFieldToAdd);
                    }
                    fundToSave.Amount = Convert.ToDecimal(row["申报金额"]);
                    fundToSave.ApprovedAmount = Convert.ToDecimal(row["审核金额"]);
                    fundToSave.Comment = string.Empty;
                    if (row.ColumnNames.Contains("备注"))
                    {
                        fundToSave.Comment = row["备注"].ToString();
                    }
                    fundToSave.Company = company;
                    fundToSave.DueAmount = 0;
                    if (row.ColumnNames.Contains("尾款"))
                    {
                        fundToSave.DueAmount = Convert.ToDecimal(row["尾款"]);
                    }
                    fundToSave.FundStatus = "申请中";
                    if (row.ColumnNames.Contains("申请状态"))
                    {
                        fundToSave.FundStatus = row["申请状态"].ToString();
                    }
                    fundToSave.FundType = fundType;
                    fundToSave.Year = Convert.ToDateTime(row["年度"].ToString() + "-1-1");


                }
                Db.SaveChanges();
                result = new Result<Dictionary<string, string>>(true, new Dictionary<string, string>() 
                                    {
                                        {"专项资金数据导入数据", countFundImported.ToString()},
                                        {"专项资金数据更新数据", countFundUpdated.ToString()},
                                        {"单位数据",countCompaniesImprted.ToString()},
                                        {"项目类别", countFundTypesImported.ToString()}
                                    });
            }
            catch (Exception ex)
            {
                result = new Result<Exception>(false, ex, ex.Message);
            }

            return result;
        }

        private string ValidateColumns(string workSheetName, List<string> columnsNeedValidate)
        {
            string errorMsg = string.Empty;
            string[] mustColumns = new string[] 
            {
                "年度",	
                "单位名称",	
                "项目类别",
                "申报金额",
                "审核金额",
                //"尾款",
                //"申请状态"
            };
            string[] validColumns = new string[] 
            { "年度",	
                "单位名称",	
                "项目类别",
                "申报金额",
                "审核金额",
                "尾款",
                "申请状态",
                "备注"
            };
            IEnumerable<string> columnsMissed = mustColumns.Where(x=>!columnsNeedValidate.Contains(x));
            if (columnsMissed.Count() > 0)
            {
                errorMsg = "缺少列：" + string.Join(", ", columnsMissed) + "，请补齐，否则无法导入。\n";
            }
            List<string> invalidColumnNames = new List<string>();
            foreach (string columnName in columnsNeedValidate)
            {
                if(!validColumns.Contains(columnName) && !columnName.StartsWith("project_"))
                {
                    invalidColumnNames.Add(columnName);
                }
            }
            if (invalidColumnNames.Count > 0)
            {
                errorMsg = errorMsg + string.Format("EXCEL工作表{0}中包含无效列：{1}，请更正，否则无法导入，有效的列名为{2}.",
                   workSheetName,
                   string.Join(", ", invalidColumnNames),
                   string.Join(", ", validColumns));
            }
            return errorMsg;
        }

        public void FundBll_ImportFunds_SmokeTest()
        {
            ImportFunds(@"D:\my\aapt\Project\XianComDeptProj\Source\BYS.XiAnComDept.WPFApp\BYS.XiAnComDept.App\FundDemo.xlsx");
        }
    }
}
